﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class ZHCX_KFYDTJBB : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    //Stopwatch sw = new Stopwatch();
    //sw.Start();
    //代码部分

    if (!Page.IsPostBack)
    {
      if (!ProjectPrivate.GetQX("35"))
      {
        Server.Transfer("~\\blank.aspx");
      }
      DataSet ds = CBSJ.GetYear();
      CommonUse.BindDDL(ds, "year", "year", DateTime.Now.Year.ToString(), ddlYear);
      //date1.Text = CommonUse.GetYC();
      //date2.Text = CommonUse.GetYM();
    }
    BindGrid();

    //sw.Stop();
    //Response.Write("本次执行共耗时:" + sw.ElapsedMilliseconds + "毫秒！");
  }

  protected void BindGrid()
  {
    DataSet ds = MergeDS();
    ASPxGridView1.DataSource = ds;
    ASPxGridView1.DataBind();
  }

  protected void btnQuery_Click(object sender, EventArgs e)
  {

  }

  protected DataSet MergeDS()
  {
    int month = DateTime.Now.Month;
    string year = ddlYear.SelectedValue;
    #region
    //    string sql = @"select a.*,
    //(case when b.cx_ysql is null then 0 else b.cx_ysql end) YSQL_1,
    //(case when b.cx_bc is null then 0 else b.cx_bc end) YSQF_1,
    //(case when c.cx_ysql is null then 0 else c.cx_ysql end) YSQL_2,
    //(case when c.cx_bc is null then 0 else c.cx_bc end) YSQF_2,
    //(case when d.cx_ysql is null then 0 else d.cx_ysql end) YSQL_3,
    //(case when d.cx_bc is null then 0 else d.cx_bc end) YSQF_3,
    //(case when e.cx_ysql is null then 0 else e.cx_bc end) YSQL_4,
    //(case when e.cx_bc is null then 0 else e.cx_bc end) YSQF_4,
    //(case when f.cx_ysql is null then 0 else f.cx_bc end) YSQL_5,
    //(case when f.cx_bc is null then 0 else f.cx_bc end) YSQF_5,
    //(case when g.cx_ysql is null then 0 else g.cx_ysql end) YSQL_6,
    //(case when g.cx_bc is null then 0 else g.cx_bc end) YSQF_6,
    //(case when h.cx_ysql is null then 0 else h.cx_ysql end) YSQL_7,
    //(case when h.cx_bc is null then 0 else h.cx_bc end) YSQF_7,
    //(case when i.cx_ysql is null then 0 else i.cx_ysql end) YSQL_8,
    //(case when i.cx_bc is null then 0 else i.cx_bc end) YSQF_8,
    //(case when j.cx_ysql is null then 0 else j.cx_ysql end) YSQL_9,
    //(case when j.cx_bc is null then 0 else j.cx_bc end) YSQF_9,
    //(case when k.cx_ysql is null then 0 else k.cx_ysql end) YSQL_10,
    //(case when k.cx_bc is null then 0 else k.cx_bc end) YSQF_10,
    //(case when l.cx_ysql is null then 0 else l.cx_ysql end) YSQL_11,
    //(case when l.cx_bc is null then 0 else l.cx_bc end) YSQF_11,
    //(case when m.cx_ysql is null then 0 else m.cx_ysql end) YSQL_12,
    //(case when m.cx_bc is null then 0 else m.cx_bc end) YSQF_12,
    //(case when n.ye is null then 0 else n.ye end) LVBZJ from 
    //
    //(select ROW_NUMBER() OVER (ORDER BY KHID) AS xh,a.* ,b.KHJB 
    //from JCXX_KHXX a,JCXX_KHJB b where a.KHJBID=b.KHJBID) a
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'01') b
    // on a.KHID=b.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'02') c
    // on a.KHID=c.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'03') d
    // on a.KHID=d.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'04') e
    // on a.KHID=e.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'05') f
    // on a.KHID=f.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'06') g
    // on a.KHID=g.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'07') h
    // on a.KHID=h.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'08') i
    // on a.KHID=i.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'09') j
    // on a.KHID=j.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'10') k
    // on a.KHID=k.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'11') l
    // on a.KHID=l.CX_KHBM
    //  left join
    //(select cx_khbm,cx_ysql,cx_bc from zhcx 
    //where convert(varchar(6),rq,112)='{0}'+'12') m
    // on a.KHID=m.CX_KHBM
    //  left join 
    //(select a.KHID,SUM(a.YJ) ye from SJLR_YJLR a group by a.KHID) n
    //  on a.khid=n.khid ";
    #endregion
    string sql = "select * from f_zhcx_yd({0})";
    sql = string.Format(sql, year);
    DataSet ds = DbHelperSQL.Query(sql);
    DataColumn dc1 = new DataColumn("HJQL", typeof(System.String));
    dc1.Expression = "YSQL_1+YSQL_2+YSQL_3+YSQL_4+YSQL_5+YSQL_6+YSQL_7+YSQL_8+YSQL_9+YSQL_10+YSQL_11+YSQL_12";
    ds.Tables[0].Columns.Add(dc1);
    DataColumn dc2 = new DataColumn("HJQF", typeof(System.String));
    dc2.Expression = "YSQF_1+YSQF_2+YSQF_3+YSQF_4+YSQF_5+YSQF_6+YSQF_7+YSQF_8+YSQF_9+YSQF_10+YSQF_11+YSQF_12";
    ds.Tables[0].Columns.Add(dc2);
    //DataSet ds1 = null, ds2 = null, ds3 = null, ds4 = null, ds5 = null, ds6 = null,
    //  ds7 = null, ds8 = null, ds9 = null, ds10 = null, ds11 = null, ds12 = null;
    //if (month > 0)
    //{
    //  ds.Tables[0].Columns.Add(new DataColumn("YSQL_1", typeof(System.String)));
    //  ds.Tables[0].Columns.Add(new DataColumn("YSQF_1", typeof(System.String)));
    //  sql = "select cx_khbm,cx_ysql,cx_bc from zhcx where convert(varchar(6),rq,112)=" + year + month;
    //  ds1 = DbHelperSQL.Query(sql);
    //  Merge(ds, ds1);
    //}
    return ds;
  }

  protected void Merge(DataSet ds, DataSet ds2)
  {
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
      for (int j = 0; j < ds2.Tables[0].Rows.Count; j++)
      {
        if (ds.Tables[0].Rows[i]["khid"] == ds2.Tables[0].Rows[j]["cx_khbm"])
        {
          ds.Tables[0].Rows[i]["ysql_1"] = ds2.Tables[0].Rows[j]["cx_ysql"];
          ds.Tables[0].Rows[i]["ysqf_1"] = ds2.Tables[0].Rows[j]["cx_ysqf"];
          break;
        }
      }
    }
  }

  protected void btnExport_Click(object sender, EventArgs e)
  {
    this.ASPxGridViewExporter1.WriteXlsToResponse();
  }
}